package br.com.ffm.praticas.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import br.com.ffm.praticas.bean.*;
import br.com.ffm.praticas.connection.ConnectionDB;

public class StateDAO {
	
	/**
	 * Method that returns all States
	 * @return List<State>
	 * @throws SQLException
	 */
	public List<State> getStates() throws SQLException {
		String query = "SELECT * FROM state ORDER BY description";
		CountryDAO countryDAO = new CountryDAO();
		List<State> list = new LinkedList<State>();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			State state = new State();
			state.setId(result.getInt("id"));
			state.setCountry(countryDAO.getCountry(result.getInt("country_id")));
			if (result.getString("description") != null)
				state.setDescription(result.getString("description"));
			if (result.getString("uf") != null)
				state.setUf(result.getString("uf"));
			list.add(state);
		}
		ConnectionDB.getInstance().close();
		return list;
	}
	
	/**
	 * Method that return a specific State for your id
	 * @param id State Identifier
	 * @return State
	 * @throws SQLException
	 */
	public State getState(int id) throws SQLException{
		String query = "SELECT * FROM state WHERE (id = " + id + ")";
		CountryDAO countryDAO = new CountryDAO();
		State state = new State();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			state = new State();
			state.setId(result.getInt("id"));
			state.setCountry(countryDAO.getCountry(result.getInt("country_id")));
			if (result.getString("description") != null)
				state.setDescription(result.getString("description"));
			if (result.getString("uf") != null)
				state.setUf(result.getString("uf"));
			ConnectionDB.getInstance().close();
			return state;
		}
		ConnectionDB.getInstance().close();
		return null;
	}
	
	/**
	 * Method that return a specific State for your description
	 * @param description State Description
	 * @return State
	 * @throws SQLException
	 */
	public State getState(String description) throws SQLException{
		String query = "SELECT * FROM state WHERE (description = '" + description + "')";
		CountryDAO countryDAO = new CountryDAO();
		State state = new State();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			state = new State();
			state.setId(result.getInt("id"));
			state.setCountry(countryDAO.getCountry(result.getInt("country_id")));
			if (result.getString("description") != null)
				state.setDescription(result.getString("description"));
			if (result.getString("uf") != null)
				state.setUf(result.getString("uf"));
			ConnectionDB.getInstance().close();
			return state;
		}
		ConnectionDB.getInstance().close();
		return null;
	}

}
